#!/bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive
PRESTO_HOME=/export/server/presto/bin/presto

${HIVE_HOME} -S -e "
--建库
create database if not exists zx_dws;
use zx_dws;

--建表
drop table if exists zx_dws.dws_attendance_count;
create table if not exists zx_dws.dws_attendance_count(
    dt       			string   	comment '日期',
    class_id          	int    	    comment '班级id',
    step       			string    	comment '时间阶段',

	normal_attendance_count    	bigint       		comment '正常出勤人数',
    normal_attendance_ratio   	decimal(38,4) 		comment '正常出勤率',
	late_count          		bigint       		comment '迟到人数',
    late_ratio   				decimal(38,4) 		comment '迟到率',
    leave_count          		bigint       		comment '请假人数',
    leave_ratio       			decimal(38,4) 		comment '请假率',
    truancy_count          		bigint       		comment '旷课人数',
    truancy_ratio       		decimal(38,4) 		comment '旷课率'
)
comment '考勤信息各时间段与汇总统计表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY');
"
wait

${PRESTO_HOME} --catalog hive --server hadoop01:8090 --execute "
use hive.zx_dws;

insert into hive.zx_dws.dws_attendance_count
with signin_step as (select
       class_id,
       student_id,
       signin_time,
       signin_date,
       morning_begin_time,
       afternoon_begin_time,
       evening_begin_time,
       class_date,
       content,
       case when date_parse(substring (signin_time,12,19),'%H:%i:%s')
            between date_add('minute',-40,date_parse(morning_begin_time,'%H:%i:%s')) and date_parse(morning_end_time,'%H:%i:%s')
            then 'morning'
            when date_parse(substring (signin_time,12,19),'%H:%i:%s')
            between date_add('minute',-40,date_parse(afternoon_begin_time,'%H:%i:%s')) and date_parse(afternoon_end_time,'%H:%i:%s')
            then 'afternoon'
            when date_parse(substring (signin_time,12,19),'%H:%i:%s')
            between date_add('minute',-40,date_parse(evening_begin_time,'%H:%i:%s')) and date_parse(evening_end_time,'%H:%i:%s')
            then 'evening'
            else 'other' end as step_type
from hive.zx_dwb.dwb_signin_wide),
signin_valid as (select
       signin_date,
       class_id,
       step_type,
       student_id,
       min(signin_time) as valid_signin_time,
       case when step_type = 'morning' then morning_begin_time
           when step_type = 'afternoon' then afternoon_begin_time
           when step_type = 'evening' then evening_begin_time
            else '00:00:00' end as begin_time
from signin_step
where step_type <> 'other'
group by signin_date,class_id,step_type,student_id,morning_begin_time,afternoon_begin_time,evening_begin_time),
signin_base as (select
       signin_date,
       class_id,
       step_type,
       student_id,
       (select studying_student_count from hive.zx_dwd.fact_class_studying_student_count
        where class_id = signin_valid.class_id and studying_date = signin_valid.signin_date) as studying_student_count,
       if(date_parse(substring (valid_signin_time,12,19),'%H:%i:%s')
           between date_add('minute',-40,date_parse(begin_time,'%H:%i:%s'))
           and date_add('minute',10,date_parse(begin_time,'%H:%i:%s')),'normal','late') as signin_type
from signin_valid),
signin_count as (select
       signin_base.signin_date as dt,
       signin_base.class_id as class_id,
       step_type as step,
       studying_student_count,
       count (student_id) as attendance_count,
       count(if(signin_type = 'normal',1,null)) as normal_attendance_count,
       count(if(signin_type = 'normal',1,null)) * 1.0000 / studying_student_count as normal_attendance_ratio,
       count(if(signin_type = 'late',1,null)) as late_count,
       count(if(signin_type = 'late',1,null)) * 1.0000 / studying_student_count as late_ratio
from signin_base
group by signin_base.signin_date,signin_base.class_id,step_type,studying_student_count
),
leave_base as (select
       id,
       class_id,
       student_id,
       begin_time,
       end_time,
       morning_begin_time,
       morning_end_time,
       afternoon_begin_time,
       afternoon_end_time,
       evening_begin_time,
       evening_end_time,
       class_date,
       content,
       (select studying_student_count from hive.zx_dwd.fact_class_studying_student_count
        where class_id = dwb_leave_apply_wide.class_id and studying_date = dwb_leave_apply_wide.class_date) as studying_student_count,
       if(date_add('minute',10,date_parse(concat(class_date,' ',morning_begin_time),'%Y-%m-%d %H:%i:%s'))
           between date_parse(begin_time,'%Y-%m-%d %H:%i:%s') and date_parse(end_time,'%Y-%m-%d %H:%i:%s'),1,0) as morning,
       if(date_add('minute',10,date_parse(concat(class_date,' ',afternoon_begin_time),'%Y-%m-%d %H:%i:%s'))
           between date_parse(begin_time,'%Y-%m-%d %H:%i:%s') and date_parse(end_time,'%Y-%m-%d %H:%i:%s'),1,0) as afternoon,
       if(date_add('minute',10,date_parse(concat(class_date,' ',evening_begin_time),'%Y-%m-%d %H:%i:%s'))
           between date_parse(begin_time,'%Y-%m-%d %H:%i:%s') and date_parse(end_time,'%Y-%m-%d %H:%i:%s'),1,0) as evening
from hive.zx_dwb.dwb_leave_apply_wide
where class_date is not null),
      leave_count as (
select
        class_date as dt,
        class_id,
        'morning' as step,
        studying_student_count,
        count(distinct if(morning = 1,student_id,null)) as leave_count,
        count(distinct if(morning = 1,student_id,null)) * 1.0000 / studying_student_count as leave_ratio
from leave_base
group by class_id, class_date,studying_student_count
union all
select
        class_date as dt,
        class_id,
        'afternoon' as step,
        studying_student_count,
        count(distinct if(afternoon = 1,student_id,null)) as leave_count,
        count(distinct if(afternoon = 1,student_id,null)) * 1.0000 / studying_student_count as leave_ratio
from leave_base
group by class_id, class_date,studying_student_count
union all
select
        class_date as dt,
        class_id,
        'evening' as step,
        studying_student_count,
        count(distinct if(evening = 1,student_id,null)) as leave_count,
        count(distinct if(evening = 1,student_id,null)) * 1.0000 / studying_student_count as leave_ratio
from leave_base
group by class_id, class_date,studying_student_count),
t as (select
       coalesce (s.dt,l.dt) as dt,
       coalesce (s.class_id,l.class_id) as class_id,
       coalesce (s.step,l.step) as step,
       coalesce (normal_attendance_count,0) as normal_attendance_count,
       coalesce (normal_attendance_ratio,0) as normal_attendance_ratio,
       coalesce (late_count,0) as late_count,
       coalesce (late_ratio,0) as late_ratio,
       coalesce (leave_count,0) as leave_count,
       coalesce (leave_ratio,0) as leave_ratio,
       coalesce (s.studying_student_count,l.studying_student_count) - coalesce(attendance_count,0) - coalesce(leave_count,0) as truancy_count,
       1.0000 - coalesce(normal_attendance_ratio + late_ratio,0) - coalesce(leave_ratio,0) as truancy_ratio
from signin_count s
full join leave_count l on s.dt = l.dt and s.class_id = l.class_id and s.step = l.step)
select
    dt,
    class_id,
    coalesce(step,'all') as step,
    sum(normal_attendance_count) as normal_attendance_count,
    avg(normal_attendance_ratio) as normal_attendance_ratio,
    sum(late_count) as late_count,
    avg(late_ratio) as late_ratio,
    sum(leave_count) as leave_count,
    avg(leave_ratio) as leave_ratio,
    sum(truancy_count) as truancy_count,
    avg(truancy_ratio) as truancy_ratio

from t
group by
grouping sets (
       (dt,class_id,step),
       (dt,class_id)
);
"

